﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Security.Claims;
using StudentManagementSystem.Models;

namespace StudentManagementSystem.Data
{
    public class DatabaseContext
    {
        private readonly string _connectionString;

        public DatabaseContext(string databasePath)
        {
            try
            {
                _connectionString = $"Data Source={databasePath};Version=3;";
                InitializeDatabase();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"数据库初始化出错: {ex.Message}");
            }
        }
        public string ConnectionString => _connectionString;
        private void InitializeDatabase()
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            CreateTable(connection, @"
                CREATE TABLE IF NOT EXISTS Schools (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Address TEXT
                )");

            CreateTable(connection, @"
                CREATE TABLE IF NOT EXISTS Classes (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    SchoolId INTEGER,
                    FOREIGN KEY (SchoolId) REFERENCES Schools(Id)
                )");

            CreateTable(connection, @"
                CREATE TABLE IF NOT EXISTS Students (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Age INTEGER,
                    ClassId INTEGER,
                    FOREIGN KEY (ClassId) REFERENCES Classes(Id)
                )");

            CreateTable(connection, @"
                CREATE TABLE IF NOT EXISTS Logs (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                    Action TEXT NOT NULL,
                    Details TEXT
                )");
        }

        private void CreateTable(SQLiteConnection connection, string createTableSql)
        {
            try
            {
                using var command = new SQLiteCommand(createTableSql, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"创建表时出错: {ex.Message}");
            }
        }

        // 学校操作方法
        public void AddSchool(School school)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var transaction = connection.BeginTransaction();
            try
            {
                using var command = new SQLiteCommand(
                    "INSERT INTO Schools (Name, Address) VALUES (@Name, @Address); " +
                    "SELECT last_insert_rowid();", connection, transaction);

                command.Parameters.AddWithValue("@Name", school.Name);
                command.Parameters.AddWithValue("@Address", school.Address);

                school.Id = Convert.ToInt32(command.ExecuteScalar());
                transaction.Commit();

                LogAction("AddSchool", $"Added school: {school.Name}");
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        public IEnumerable<School> GetAllSchools()
        {
            var schools = new List<School>();

            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("SELECT * FROM Schools", connection);
            using var reader = command.ExecuteReader();

            while (reader.Read())
            {
                schools.Add(new School
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Address = reader.IsDBNull(2) ? null : reader.GetString(2)
                });
            }

            return schools;
        }

        public void UpdateSchool(School school)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "UPDATE Schools SET Name = @Name, Address = @Address WHERE Id = @Id", connection);

            command.Parameters.AddWithValue("@Id", school.Id);
            command.Parameters.AddWithValue("@Name", school.Name);
            command.Parameters.AddWithValue("@Address", school.Address);

            command.ExecuteNonQuery();

            LogAction("UpdateSchool", $"Updated school: {school.Name} (ID: {school.Id})");
        }

        public void DeleteSchool(int schoolId)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            // 先删除关联的班级和学生
            using (var classCommand = new SQLiteCommand("SELECT Id FROM Classes WHERE SchoolId = @SchoolId", connection))
            {
                classCommand.Parameters.AddWithValue("@SchoolId", schoolId);
                using var classReader = classCommand.ExecuteReader();

                var classIds = new List<int>();
                while (classReader.Read())
                {
                    classIds.Add(classReader.GetInt32(0));
                }

                // 删除关联的学生
                foreach (var classId in classIds)
                {
                    using var studentCommand = new SQLiteCommand("DELETE FROM Students WHERE ClassId = @ClassId", connection);
                    studentCommand.Parameters.AddWithValue("@ClassId", classId);
                    studentCommand.ExecuteNonQuery();
                }

                // 删除班级
                using var deleteClassCommand = new SQLiteCommand("DELETE FROM Classes WHERE SchoolId = @SchoolId", connection);
                deleteClassCommand.Parameters.AddWithValue("@SchoolId", schoolId);
                deleteClassCommand.ExecuteNonQuery();
            }

            // 删除学校
            using var command = new SQLiteCommand("DELETE FROM Schools WHERE Id = @Id", connection);
            command.Parameters.AddWithValue("@Id", schoolId);
            command.ExecuteNonQuery();

            LogAction("DeleteSchool", $"Deleted school with ID: {schoolId}");
        }

        // 班级操作方法
        public void AddClass(Class @class)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "INSERT INTO Classes (Name, SchoolId) VALUES (@Name, @SchoolId); " +
                "SELECT last_insert_rowid();", connection);

            command.Parameters.AddWithValue("@Name", @class.Name);
            command.Parameters.AddWithValue("@SchoolId", @class.SchoolId);

            @class.Id = Convert.ToInt32(command.ExecuteScalar());

            LogAction("AddClass", $"Added class: {(@class.Name)} in school ID: {(@class.SchoolId)}");
        }

        public IEnumerable<Class> GetClassesBySchoolId(int schoolId)
        {
            var classes = new List<Class>();

            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("SELECT * FROM Classes WHERE SchoolId = @SchoolId", connection);
            command.Parameters.AddWithValue("@SchoolId", schoolId);

            using var reader = command.ExecuteReader();

            while (reader.Read())
            {
                classes.Add(new Class
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    SchoolId = reader.GetInt32(2)
                });
            }
            return classes;
        }

        public void UpdateClass(Class @class)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "UPDATE Classes SET Name = @Name, SchoolId = @SchoolId WHERE Id = @Id", connection);

            command.Parameters.AddWithValue("@Id", @class.Id);
            command.Parameters.AddWithValue("@Name", @class.Name);
            command.Parameters.AddWithValue("@SchoolId", @class.SchoolId);

            command.ExecuteNonQuery();

            LogAction("UpdateClass", $"Updated class: {(@class.Name)} (ID: {(@class.Id)})");
        }

        public void DeleteClass(int classId)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            // 先删除关联的学生
            using var studentCommand = new SQLiteCommand("DELETE FROM Students WHERE ClassId = @ClassId", connection);
            studentCommand.Parameters.AddWithValue("@ClassId", classId);
            studentCommand.ExecuteNonQuery();

            // 删除班级
            using var command = new SQLiteCommand("DELETE FROM Classes WHERE Id = @Id", connection);
            command.Parameters.AddWithValue("@Id", classId);
            command.ExecuteNonQuery();

            LogAction("DeleteClass", $"Deleted class with ID: {classId}");
        }

        // 学生操作方法
        public void AddStudent(Student student)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "INSERT INTO Students (Name, Age, ClassId) VALUES (@Name, @Age, @ClassId); " +
                "SELECT last_insert_rowid();", connection);

            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@Age", student.Age);
            command.Parameters.AddWithValue("@ClassId", student.ClassId);

            student.Id = Convert.ToInt32(command.ExecuteScalar());

            LogAction("AddStudent", $"Added student: {student.Name} (Age: {student.Age}) in class ID: {student.ClassId}");
        }

        public IEnumerable<Student> GetStudentsByClassId(int classId)
        {
            var students = new List<Student>();

            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("SELECT * FROM Students WHERE ClassId = @ClassId", connection);
            command.Parameters.AddWithValue("@ClassId", classId);

            using var reader = command.ExecuteReader();

            while (reader.Read())
            {
                students.Add(new Student
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Age = reader.GetInt32(2),
                    ClassId = reader.GetInt32(3)
                });
            }

            return students;
        }

        public void UpdateStudent(Student student)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "UPDATE Students SET Name = @Name, Age = @Age, ClassId = @ClassId WHERE Id = @Id", connection);

            command.Parameters.AddWithValue("@Id", student.Id);
            command.Parameters.AddWithValue("@Name", student.Name);
            command.Parameters.AddWithValue("@Age", student.Age);
            command.Parameters.AddWithValue("@ClassId", student.ClassId);

            command.ExecuteNonQuery();

            LogAction("UpdateStudent", $"Updated student: {student.Name} (ID: {student.Id})");
        }

        public void DeleteStudent(int studentId)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("DELETE FROM Students WHERE Id = @Id", connection);
            command.Parameters.AddWithValue("@Id", studentId);
            command.ExecuteNonQuery();

            LogAction("DeleteStudent", $"Deleted student with ID: {studentId}");
        }

        // 日志操作方法
        public void LogAction(string action, string details)
        {
            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand(
                "INSERT INTO Logs (Action, Details) VALUES (@Action, @Details)", connection);

            command.Parameters.AddWithValue("@Action", action);
            command.Parameters.AddWithValue("@Details", details);

            command.ExecuteNonQuery();
        }

        public IEnumerable<LogEntry> GetAllLogs()
        {
            var logs = new List<LogEntry>();

            using var connection = new SQLiteConnection(_connectionString);
            connection.Open();

            using var command = new SQLiteCommand("SELECT * FROM Logs ORDER BY Timestamp DESC", connection);
            using var reader = command.ExecuteReader();

            while (reader.Read())
            {
                logs.Add(new LogEntry
                {
                    Id = reader.GetInt32(0),
                    Timestamp = reader.GetDateTime(1),
                    Action = reader.GetString(2),
                    Details = reader.IsDBNull(3) ? null : reader.GetString(3)
                });
            }

            return logs;
        }
    }
}